home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
powerb5.zip
/
P5SPR004.TIP
< prev
next >
Wrap
Text File
|
1993-06-01
|
2KB
|
58 lines
Occasionally I need to write a 1-2-3 macro that loops
through a list of items in a worksheet. For instance, if I'm
printing mailing labels from a list of clients, I might want
to move each address in turn to a "scratch" area, format it,
then send it along to the printer. The lists are often very
long, so it doesn't pay to write a line of code for each of
them.
In this example, a looping macro that starts in cell C3
copies the first name in the A3..A7 range to A20, prints the
name, then repeats the process with the next name down until
the bottom of the list; there are only five names in this
example, but the macro as written handles up to 300.
A B C D E F G
1 LIST MACRO TO SEQUENTIALLY COPY ELEMENTS OF A LIST
2
3 Jan \P {LET G13,0}~ sets LOOPCOUNT to 0
4 Richie /CA9~A20~ copies one list element to target
5 Ed /PPRA20..A20~GQ prints each list element
6 Paula {LET G13,G13+1}~ increments LOOPCOUNTER
7 Anna {IF @COUNT(A3..A300)<=G13}{QUIT} QUITs loop at end of list
8 {BRANCH C4}~ loops back to start
9
10
11
12 LIST OF MACRO TEXT SEGMENTS LOOPCOUNTER
13 /CA 6 # of loop executions
14 A20 9 row # referenced by MACRO (C4)
15 ~
I use 1-2-3's text concatenation operator (represented by an
ampersand) to create a reference to a new cell address each
time the loop cycles. The formula in cell C4 creates a
concatenated text string from segments in B13..B15 and a
loop counter in G14. Each time the loop executes, the string
in C4 changes. The iteration count is displayed in cell G13
and converted to a string in G14 via the @STRING() function.
Rick Brown
Marietta, Ohio
Editor's Note: Mr. Brown's sample worksheet is included in
the file P5SPR\LOOP.WK1 on your PowerBase *.* Volume 5
diskette. Note that C3, the cell where the macro starts
running, has the label \P -- to which 1-2-3 automatically
branches when <Alt>-P is pressed.
Title: Looping in Lotus
Category: SPR
Issue Date: June, 1992
Editor: Brett Glass
Supplementary Files: P5SPR\LOOP.WK1
Filename: P5SPR004.TIP